<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  
  <title>使用sqlldr导入上亿条数据到oracle中 | Hexo</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="导入数据">
<meta property="og:type" content="article">
<meta property="og:title" content="使用sqlldr导入上亿条数据到oracle中">
<meta property="og:url" content="http://example.com/2021/03/13/sqlldr%E5%AF%BC%E5%85%A5%E5%A4%A7%E9%87%8F%E6%95%B0%E6%8D%AE%E5%88%B0oracle%E6%95%B0%E6%8D%AE%E5%BA%93/index.html">
<meta property="og:site_name" content="Hexo">
<meta property="og:description" content="导入数据">
<meta property="og:locale" content="en_US">
<meta property="og:image" content="c:/Users/Anyon/AppData/Roaming/Typora/typora-user-images/image-20210204170041753.png">
<meta property="article:published_time" content="2021-03-13T14:09:39.460Z">
<meta property="article:modified_time" content="2021-03-13T14:19:51.499Z">
<meta property="article:author" content="John Doe">
<meta property="article:tag" content="学习笔记">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="c:/Users/Anyon/AppData/Roaming/Typora/typora-user-images/image-20210204170041753.png">
  
    <link rel="alternate" href="/atom.xml" title="Hexo" type="application/atom+xml">
  
  
    <link rel="shortcut icon" href="/favicon.png">
  
  
    
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/typeface-source-code-pro@0.0.71/index.min.css">

  
  
<link rel="stylesheet" href="/css/style.css">

  
    
<link rel="stylesheet" href="/fancybox/jquery.fancybox.min.css">

  
<meta name="generator" content="Hexo 5.4.0"></head>

<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/" id="logo">Hexo</a>
      </h1>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/">Home</a>
        
          <a class="main-nav-link" href="/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
          <a id="nav-rss-link" class="nav-icon" href="/atom.xml" title="RSS Feed"></a>
        
        <a id="nav-search-btn" class="nav-icon" title="Search"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="http://example.com"></form>
      </div>
    </div>
  </div>
</header>

      <div class="outer">
        <section id="main"><article id="post-sqlldr导入大量数据到oracle数据库" class="h-entry article article-type-post" itemprop="blogPost" itemscope itemtype="https://schema.org/BlogPosting">
  <div class="article-meta">
    <a href="/2021/03/13/sqlldr%E5%AF%BC%E5%85%A5%E5%A4%A7%E9%87%8F%E6%95%B0%E6%8D%AE%E5%88%B0oracle%E6%95%B0%E6%8D%AE%E5%BA%93/" class="article-date">
  <time class="dt-published" datetime="2021-03-13T14:09:39.460Z" itemprop="datePublished">2021-03-13</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/oracle/">oracle</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="p-name article-title" itemprop="headline name">
      使用sqlldr导入上亿条数据到oracle中
    </h1>
  

      </header>
    
    <div class="e-content article-entry" itemprop="articleBody">
      
        <p>接到一个任务，要把160G的数据导入到公司oracle服务器上。最开始以为就是导数据这么简单的任务，虽然没以前没用过oracle没导过数据，但是在网上搜一下方法不久行了？我信心满满地接了下来，没想到这么简单的任务也有如此多的坑，下面把导数据过程中的踩坑和弯路记录下来，共勉。</p>
<p>第一步当然是选择用啥工具导入数据库，常用的navicat或pl/sql感觉速度上都不太行，在网上看到导入大量数据一般用sqlloader这个oracle自带的工具。sqlloader就是sqlldr，在oracle官网上可以下载客户端。下载地址：<a target="_blank" rel="noopener" href="https://www.oracle.com/cn/database/technologies/instant-client/winx64-64-downloads.html%E3%80%82">https://www.oracle.com/cn/database/technologies/instant-client/winx64-64-downloads.html。</a></p>
<p><strong>踩坑1</strong>：找不到对应版本的sqlloader。公司服务器的oracle版本是11g，然而11g的版本在官网上并没有sqlloader。网上好多博客说精简版客户端没有sqlloader，但是在哪找非精简版的客户端呢？还好在参考了</p>
<p>的内容，高版本的sqlloader同样支持11g的oracle服务器版。于是下载了12.2.0.1.0版本的sqlloader，下载地址：<a target="_blank" rel="noopener" href="https://www.oracle.com/cn/database/technologies/instant-client/winx64-64-downloads.html#license-lightbox%E3%80%82">https://www.oracle.com/cn/database/technologies/instant-client/winx64-64-downloads.html#license-lightbox。</a></p>
<p><strong>踩坑2</strong>：下载后解压，把解压后的路径加到环境变量中，即可在cmd命令行中测试sqlldr是否正常工作。但是我在cmd命令行中测试sqlldr总是报这样的错误：无法定位程序输入点域动态连接库上。这个错误是sqlldr和原有的oracle client版本不一致造成的。我电脑之前安装的是instantclient-basic-windows.x64-11.2.0.4.0。于是卸载之前版本的basic客户端，安装instantclient-basic-windows.x64-12.2.0.1.0。终于，sqlldr的环境搞定了！测试页面如下：</p>
<p><img src="C:\Users\Anyon\AppData\Roaming\Typora\typora-user-images\image-20210204170041753.png" alt="image-20210204170041753"></p>
<p><strong>踩坑3</strong>：sqlldr的环境搞定后，下一步就是连接数据库了。由于oracle数据库安装在服务器上，所以连接的语法是，在命令行输入：</p>
<p><code>sqlldr username/password@host:port/service_name</code></p>
<p>注意<code>sqlldr</code>后面跟的是主机字符串，而主机字符串最后一项得是service_name而不是SID。我总觉得用SID也是可以连接的，但是没找到相应的连接语句。</p>
<p><strong>踩坑4</strong>：如果能正常连接，下一步需要指定控制文件，也就是control文件。当然也可以和上一步命令一起输入。control文件是sqlldr最为重要的一个文件，它指定了加载选项，加载数据的内容，加载方式等。control文件写的好，后面就一点坑都不用踩！！新建一个后缀为.ctl的文件作为控制文件，例：data.ctl，内容如下</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">OPTIONS (<span class="keyword">skip</span><span class="operator">=</span><span class="number">1</span>)  <span class="comment">--control文件中，--是注释符，options可以指定关键字的默认值，有效的关键字可以通过在命令行中输入sqlldr查看，常用的有skip,rows,direct等</span></span><br><span class="line">LODA DATA <span class="comment">--加载数据</span></span><br><span class="line">INFILE &quot;data1.csv&quot;  <span class="comment">--INFILE关键字指定从哪些文件中加载数据，可以只写一个文件，如果从多个文件加载的话就多写几行</span></span><br><span class="line">INFILE &quot;data2.csv&quot;</span><br><span class="line">INFILE &quot;data3.csv&quot;</span><br><span class="line"><span class="keyword">TRUNCATE</span>  <span class="comment">--先把表清空</span></span><br><span class="line"><span class="keyword">INTO</span> <span class="keyword">TABLE</span> DATA  <span class="comment">--往DATA表中插入数据</span></span><br><span class="line">Field terminated <span class="keyword">by</span> &quot;,&quot;  <span class="comment">--字段以，（逗号）隔开，因为是从csv文件中导入的，csv文件是以逗号分隔的文本文件</span></span><br><span class="line">Optionally enclosed <span class="keyword">by</span> <span class="string">&#x27;&quot;&#x27;</span>  <span class="comment">--数据中每个字段用&quot;框起，这个地方非常重要。我最开始以为有了上一行字段以逗号分开后就不用这句话了，后来才发现我还是太年轻</span></span><br><span class="line"><span class="keyword">trailing</span> nullcols  <span class="comment">--没有内容的字段记为空</span></span><br><span class="line">(</span><br><span class="line">字段<span class="number">1</span>，  <span class="comment">--最后一个字段不用加逗号，如果对应数据库中的字段类型是date，则需要用函数DATE转化成对应的格式</span></span><br><span class="line">字段<span class="number">2</span>，</span><br><span class="line">字段<span class="number">3</span> <span class="type">DATE</span> <span class="string">&#x27;YYYY/MM/DD HH24:MI:SS&#x27;</span>,</span><br><span class="line">...</span><br><span class="line">)</span><br></pre></td></tr></table></figure>

<p><strong>弯路1</strong>: 如果一开始就按照上述的格式编写控制文件，就不会有任何问题。但是我自作聪明把<code>Optionally enclosed by &#39;&quot;&#39;</code>这句话删掉了。原始数据是csv格式的，其中每个文件的大小在15G左右，因此我先用pandas导出了前1000行保存到了<code>data1000.csv</code>中，看看数据长啥样。每一行的格式都是和数据库中的一样，看样子没啥问题。于是在命令行中输入</p>
<p><code>sqlldr username/password@host:port/service_name control=data.ctl</code></p>
<p>其中控制文件中指定了从<code>data1000.csv</code>文件加载数据。不出意外的报错了，原因是pandas导出数据时，自动加上了一列行索引，于是sqlldr加载数据时字段没对应上。这种情况对应着csv文件中的列数多余数据库表中的列数，需要在多余的列对应位置加上 FILLER关键字进行跳过，如下：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">字段<span class="number">1</span>，</span><br><span class="line">virtual_column FILLER  <span class="comment">--跳过这一列</span></span><br><span class="line">字段<span class="number">2</span>，</span><br></pre></td></tr></table></figure>

<p>跳过索引列之后，从pandas导出的这1000行数据就能正常上传了！！</p>
<p>于是在控制文件中指定加载源文件，在命令行执行后又又又报错了！查看日志，报如下错误：</p>
<blockquote>
<p>记录 1: 被拒绝 - 表 DATA 的列 ID 出现错误。 </p>
<p>ORA-01722: 无效数字</p>
</blockquote>
<p>这个报错的原因是从csv文件中读出来的字段都是字符串，而ID这个字段的类型是Number，所以类型不匹配造成的。那为啥用pandas导出的前1000行数据就可以正常加载呢？原因是pandas在导出数据是自动做了转换，把数字构成的字符串保存成了字符串格式。那我在控制文件中指定一下字段类型是不是也一样的效果哈哈哈，我试了以下格式的控制文件：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ID Number(<span class="number">16</span>)</span><br></pre></td></tr></table></figure>

<p>然后还是报错，结果如下：</p>
<blockquote>
<p>SQL*Loader-350: Syntax error at line 9. Expecting “,” or “)”, found “NUMBER”. ID NUMBER(16),</p>
</blockquote>
<p>为什么字段后面能写DATE类型的格式，不能写Number呢，这是因为DATE是sqlldr中的一个转换函数，而Number不是。有许多博客建议在该字段后面加上整数的关键字：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">ID <span class="type">INTEGER</span>  <span class="comment">--表示插入的数据是二进制</span></span><br><span class="line"><span class="comment">-- 或者</span></span><br><span class="line">ID <span class="type">INTEGER</span> <span class="keyword">EXTERNAL</span> <span class="comment">--表示插入的数据是string（这个地方我还是有疑惑）</span></span><br></pre></td></tr></table></figure>

<p>我试了上面的两种写法，还是报ORA-01722的错。在百思不得其解的情况下我突然萌生了一个笨办法，既然用pandas导出的前1000行记录能插入，索性我就把所有的源数据都用pandas转换一下，不就可以导入了吗？说干就干，立马写了一个python脚本，丢在电脑上执行了一晚上。</p>
<p><strong>弯路2</strong>: 好不容易用pandas把所有数据都转换完成了，这个时候又出现了新的问题！某些源数据中字段是中文，编码格式得用GB2312；有些字段中还有换行符，pandas导出时会出现把一行数据导出为2行的情况。我这个弯路越走越弯了啊。于是我又写了一个转换判断字段中是否存在换行符的脚本，把含有换行符的几个源文件再转换一遍。</p>
<p><strong>踩坑5</strong>: 对于不含中文且字段中没有换行符的文件，经过pandas转换后已经可以上传到服务器了！赶紧执行上传命令</p>
<p><code>sqlldr username/password@host:port/service_name control=data.ctl</code></p>
<p>这个转换后的数据文件有32G，我就眼睁睁看着它以大概250万条/小时的速度导入，这导到猴年马月。</p>
<p><strong>最终解决办法</strong>:  在踩过了前面的坑和弯路后，我忽然发现控制文件中<code>Optionally enclosed by &#39;&quot;&#39;</code>似乎有奇妙的作用，加上这句话之后，那些被双引号括起来的数字就可以被当作数字接收而不是被当作字符串拒绝了！这么说我用pandas转换文件完全是多次一举？？并且以打开直接路径加载的方式要上面的加载方式快的多！所以最终控制文件的内容如下：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">OPTIONS (direct<span class="operator">=</span><span class="literal">true</span>, <span class="keyword">skip</span><span class="operator">=</span><span class="number">1</span>, BINDSIZE<span class="operator">=</span><span class="number">20971520</span>,READSIZE<span class="operator">=</span><span class="number">20971520</span>,<span class="keyword">ROWS</span><span class="operator">=</span><span class="number">500000</span>)  <span class="comment">--control文件中，--是注释符，options可以指定关键字的默认值，有效的关键字可以通过在命令行中输入sqlldr查看，常用的有skip,rows,direct等。direct=true就是指以直接路径加载</span></span><br><span class="line">LODA DATA <span class="comment">--加载数据</span></span><br><span class="line">INFILE &quot;data1.csv&quot;  <span class="comment">--INFILE关键字指定从哪些文件中加载数据，可以只写一个文件，如果从多个文件加载的话就多写几行</span></span><br><span class="line">INFILE &quot;data2.csv&quot;</span><br><span class="line">INFILE &quot;data3.csv&quot;</span><br><span class="line"><span class="keyword">TRUNCATE</span>  <span class="comment">--先把表清空</span></span><br><span class="line"><span class="keyword">INTO</span> <span class="keyword">TABLE</span> DATA  <span class="comment">--往DATA表中插入数据</span></span><br><span class="line">Field terminated <span class="keyword">by</span> &quot;,&quot;  <span class="comment">--字段以，（逗号）隔开，因为是从csv文件中导入的，csv文件是以逗号分隔的文本文件</span></span><br><span class="line">Optionally enclosed <span class="keyword">by</span> <span class="string">&#x27;&quot;&#x27;</span>  <span class="comment">--数据中每个字段用&quot;框起，这个地方非常重要。</span></span><br><span class="line"><span class="keyword">trailing</span> nullcols  <span class="comment">--没有内容的字段记为空</span></span><br><span class="line">(</span><br><span class="line">字段<span class="number">1</span>，  <span class="comment">--最后一个字段不用加逗号，如果对应数据库中的字段类型是date，则需要用函数DATE转化成对应的格式</span></span><br><span class="line">字段<span class="number">2</span>，</span><br><span class="line">字段<span class="number">3</span> <span class="type">DATE</span> <span class="string">&#x27;YYYY/MM/DD HH24:MI:SS&#x27;</span>,</span><br><span class="line">...</span><br><span class="line">)</span><br></pre></td></tr></table></figure>

<p>这种直接路径加载的方式也可选择并行，需要把<code>TRUNCATE</code>改为<code>APPEND</code>。不过我没有用并行的方式，一个多小时就插入完了所有数据。一张表有1.78亿条记录，这个速度已经很可以了。</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://example.com/2021/03/13/sqlldr%E5%AF%BC%E5%85%A5%E5%A4%A7%E9%87%8F%E6%95%B0%E6%8D%AE%E5%88%B0oracle%E6%95%B0%E6%8D%AE%E5%BA%93/" data-id="ckm7t5pct0001hottg9kc6k5h" data-title="使用sqlldr导入上亿条数据到oracle中" class="article-share-link">Share</a>
      
      
      
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/" rel="tag">学习笔记</a></li></ul>

    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/2021/03/13/django%E8%87%AA%E5%AE%9A%E4%B9%89%E6%97%A5%E5%BF%97/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Newer</strong>
      <div class="article-nav-title">
        
          django自定义日志
        
      </div>
    </a>
  
  
    <a href="/2021/03/13/windows%E7%8E%AF%E5%A2%83%E4%B8%8B%E5%AE%89%E8%A3%85pyscipopt/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Older</strong>
      <div class="article-nav-title">windows下安装pyscipopy</div>
    </a>
  
</nav>

  
</article>


</section>
        
          <aside id="sidebar">
  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Categories</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/categories/SCIP/">SCIP</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/django/">django</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/leetcode/">leetcode</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/oracle/">oracle</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tags</h3>
    <div class="widget">
      <ul class="tag-list" itemprop="keywords"><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/" rel="tag">学习笔记</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tag Cloud</h3>
    <div class="widget tagcloud">
      <a href="/tags/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/" style="font-size: 10px;">学习笔记</a>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Archives</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/03/">March 2021</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Recent Posts</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/2021/03/13/224.%E5%9F%BA%E6%9C%AC%E8%AE%A1%E7%AE%97%E5%99%A8/">基本计算器</a>
          </li>
        
          <li>
            <a href="/2021/03/13/django%E8%87%AA%E5%AE%9A%E4%B9%89%E6%97%A5%E5%BF%97/">django自定义日志</a>
          </li>
        
          <li>
            <a href="/2021/03/13/sqlldr%E5%AF%BC%E5%85%A5%E5%A4%A7%E9%87%8F%E6%95%B0%E6%8D%AE%E5%88%B0oracle%E6%95%B0%E6%8D%AE%E5%BA%93/">使用sqlldr导入上亿条数据到oracle中</a>
          </li>
        
          <li>
            <a href="/2021/03/13/windows%E7%8E%AF%E5%A2%83%E4%B8%8B%E5%AE%89%E8%A3%85pyscipopt/">windows下安装pyscipopy</a>
          </li>
        
          <li>
            <a href="/2021/03/13/hello-world/">Hello World</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      
      &copy; 2021 John Doe<br>
      Powered by <a href="https://hexo.io/" target="_blank">Hexo</a>
    </div>
  </div>
</footer>

    </div>
    <nav id="mobile-nav">
  
    <a href="/" class="mobile-nav-link">Home</a>
  
    <a href="/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    


<script src="/js/jquery-3.4.1.min.js"></script>



  
<script src="/fancybox/jquery.fancybox.min.js"></script>




<script src="/js/script.js"></script>





  </div>
</body>
</html>